﻿USE [thoitrang]
GO
/****** Object:  StoredProcedure [dbo].[a_CatTree_sel_mcid_scid]    Script Date: 03/04/2009 08:17:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].a_CatTree_sel
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 1/8/2009 9:06:45 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].[a_CatTree_sel_mcid_scid]
	@CatID int,
	@SubCatID int
AS

SELECT
	[ID],
	[CatID],
	[SubCatID],
	[CatSub2ID]
FROM
	[dbo].[a_CatTree]
WHERE
	[CatID] = @CatID and [SubCatID]=@SubCatID


USE [thoitrang]
GO
/****** Object:  StoredProcedure [dbo].[a_CatTree_selList_mcid]    Script Date: 03/04/2009 08:18:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].a_CatTree_selList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 1/8/2009 9:06:45 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].[a_CatTree_selList_mcid]
	@CatID int,
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #Tempa_CatTree (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ID int	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
IF @PageSize > 0
   SET ROWCOUNT @Top
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #Tempa_CatTree ([ID]) SELECT '
SET @sql = @sql + ' [ID] FROM [dbo].[a_CatTree]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)
SET ROWCOUNT 0

SELECT @TotalRecords = COUNT(*) FROM [a_CatTree]

SELECT
	[dbo].[a_CatTree].[ID],
	[CatID],
	[SubCatID],
	[CatSub2ID]
FROM
	#Tempa_CatTree AS tblTemp JOIN [dbo].[a_CatTree] ON
	tblTemp.ID = [dbo].[a_CatTree].ID 	
WHERE [dbo].[a_CatTree].CatId=@CatID AND (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #Tempa_CatTree



USE [thoitrang]
GO
/****** Object:  StoredProcedure [dbo].[a_CatTree_selList_scid]    Script Date: 03/04/2009 08:18:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].a_CatTree_selList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 1/8/2009 9:06:45 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].[a_CatTree_selList_scid]
	@SubCatID int,
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #Tempa_CatTree (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ID int	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
IF @PageSize > 0
   SET ROWCOUNT @Top
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #Tempa_CatTree ([ID]) SELECT '
SET @sql = @sql + ' [ID] FROM [dbo].[a_CatTree]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)
SET ROWCOUNT 0

SELECT @TotalRecords = COUNT(*) FROM [a_CatTree]

SELECT
	[dbo].[a_CatTree].[ID],
	[CatID],
	[SubCatID],
	[CatSub2ID]
FROM
	#Tempa_CatTree AS tblTemp JOIN [dbo].[a_CatTree] ON
	tblTemp.ID = [dbo].[a_CatTree].ID 	
WHERE [dbo].[a_CatTree].SubCatId=@SubCatID AND (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #Tempa_CatTree




